﻿Imports Entidad
Imports System.Data
Imports System.Data.SqlClient
Public Class ProgramacionDatos

    Sub registrarProgramacion(prog As Programacion)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "INSERT INTO Programacion(DCI_Codigo,VEH_Codigo,TUR_Codigo,PRO_Fecha,PRO_FechaAsig,PRO_Orden,PRO_Activo) VALUES(@DCI,@VEH,@TUR,@FEC,GETDATE(),@ORD,'1')"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@DCI", prog.DCI_Codigo)
            .Parameters.AddWithValue("@VEH", prog.VEH_Codigo)
            .Parameters.AddWithValue("@TUR", prog.TUR_Codigo)
            .Parameters.AddWithValue("@FEC", prog.PRO_Fecha)
            .Parameters.AddWithValue("@ORD", prog.PRO_Orden)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Function listarAsignaciones(fecha As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT v.VEH_Codigo,t.TUR_Codigo,CONVERT(VARCHAR(10), p.PRO_Fecha, 103) AS Fecha,v.VEH_Placa,t.TUR_Descripcion AS Turno,ma.MAR_Descripcion+'/'+m.MOD_Descripcion AS MARMOD, " &
                              "dbo.FNX_AgruparRutas(v.VEH_Codigo,CONVERT(VARCHAR(10), p.PRO_Fecha, 103),t.TUR_Codigo) AS Rutas, " &
                              "SUM(dcb.DCB_PesoNeto) AS PesoNetoPed, '0.00' AS VolumenPed, v.VEH_PesoNeto AS PesoNetoVeh, v.VEH_Volumen as VolumenVeh, " &
                              "(CASE WHEN SUM(dcb.DCB_PesoNeto) > v.VEH_PesoNeto THEN '1' ELSE '0' END) AS Verificar " &
                              "FROM Programacion p " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.DCI_Codigo = p.DCI_Codigo " &
                              "INNER JOIN Estado e " &
                              "ON e.EST_Codigo = dc.DCI_Estado AND e.EST_Activo = '1' AND (e.EST_Codigo = '09' OR e.EST_Codigo = '10') " &
                              "INNER JOIN Vehiculo v " &
                              "ON v.VEH_Codigo = p.VEH_Codigo AND v.VEH_Activo = '1' " &
                              "INNER JOIN Modelo m " &
                              "ON m.MOD_Codigo = v.MOD_Codigo AND m.MOD_Activo = '1' " &
                              "INNER JOIN Marca ma " &
                              "ON ma.MAR_Codigo = m.MAR_Codigo AND ma.MAR_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Bulto dcb " &
                              "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo AND dcb.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                              "INNER JOIN Turno t " &
                              "ON t.TUR_Codigo = p.TUR_Codigo AND t.TUR_Activo = '1' " &
                              "WHERE p.PRO_Fecha = '" & fecha & "' AND p.PRO_Activo = '1' AND p.PRO_FecSalida IS NULL " &
                              "GROUP BY v.VEH_Codigo,t.TUR_Codigo,p.PRO_Fecha,v.VEH_Placa,ma.MAR_Descripcion,m.MOD_Descripcion, " &
                              "v.VEH_PesoNeto,v.VEH_Volumen,t.TUR_Descripcion"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarPedidosXProgramacion(vehiculo As String, fecha As String, turno As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT '1' AS CHECKB, dc.DCI_Codigo, c.CLI_Descripcion, ISNULL(d.DIS_Descripcion,'') AS Distrito, e.EST_Abreviatura, " &
                              "r.RUT_Descripcion, COUNT(dcb.DCI_Codigo) AS CantBultos, ISNULL(tabla1.ItemsLargos,0) AS CantLargos, " &
                              "SUM(dcb.DCB_PesoBruto) AS PesoTotal, '0.00' AS Volumen, p.PRO_Orden, dc.DCI_Estado " &
                              "FROM Programacion p " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.DCI_Codigo = p.DCI_Codigo " &
                              "INNER JOIN Cliente c " &
                              "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "LEFT JOIN ClienteEntrega ce " &
                              "ON ce.CLI_Codigo = c.CLI_Codigo AND ce.CLEN_Activo = '1' " &
                              "LEFT JOIN Distrito d " &
                              "ON d.DIS_Codigo = ce.DIS_Codigo AND d.DIS_Activo = '1' " &
                              "INNER JOIN Estado e " &
                              "ON e.EST_Codigo = dc.DCI_Estado AND e.EST_Activo = '1' AND (e.EST_Codigo = '09' OR e.EST_Codigo = '10') " &
                              "INNER JOIN Ruta r " &
                              "ON r.RUT_Codigo = dc.RUT_Codigo " &
                              "INNER JOIN Documento_Cliente_Bulto dcb " &
                              "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo AND dcb.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                              "LEFT JOIN (SELECT dc.DCI_Codigo,COUNT(*) AS Cantidad " &
                              "FROM Documento_Cliente dc " &
                              "INNER JOIN Documento_Cliente_Detalle dcd " &
                              "ON dc.DCI_Codigo = dcd.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                              "GROUP BY dc.DCI_Codigo) AS tabla " &
                              "ON tabla.DCI_Codigo = dc.DCI_Codigo " &
                              "LEFT JOIN (SELECT dcad.CIA_Cliente, dcad.SUC_Cliente, dcad.TDOC_Cliente, dcad.DCI_Codigo, COUNT(*) AS ItemsLargos " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN VW_VistaDocumentoAlmacen da " &
                              "ON da.CIA = dcad.CIA_Almacen AND da.SUC_Codigo = dcad.SUC_Almacen AND da.TDOC_Codigo = dcad.TDOC_Almacen " &
                              "AND da.DCA_Codigo = dcad.DCA_Codigo AND da.DAD_Codigo = dcad.DAD_Codigo AND da.BAN_Codigo = '00000' " &
                              "GROUP BY dcad.CIA_Cliente, dcad.SUC_Cliente, dcad.TDOC_Cliente, dcad.DCI_Codigo) tabla1 " &
                              "ON tabla1.CIA_Cliente = dc.CIA AND tabla1.SUC_Cliente = dc.SUC_Codigo AND tabla1.TDOC_Cliente = dc.TDOC_Codigo " &
                              "AND tabla1.DCI_Codigo = dc.DCI_Codigo " &
                              "WHERE p.VEH_Codigo = " & vehiculo & " AND p.TUR_Codigo = " & turno & " AND p.PRO_Fecha = '" & fecha & "' AND p.PRO_Activo = '1' " &
                              "GROUP BY dc.DCI_Codigo, c.CLI_Descripcion,d.DIS_Descripcion,e.EST_Abreviatura,r.RUT_Descripcion, " &
                              "tabla.Cantidad,PRO_Orden,dc.DCI_Estado,tabla1.ItemsLargos " &
                              "ORDER BY p.PRO_Orden"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub eliminarProgramacion(vehiculo As String, fecha As String, turno As String, dciCodigo As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = ""

        If dciCodigo = "" Then
            query = "UPDATE Programacion " &
                    "SET PRO_Activo = '0' " &
                    "WHERE VEH_Codigo = @VEH AND CONVERT(VARCHAR(10), PRO_Fecha, 103) = @FEC " &
                    "AND TUR_Codigo = @TUR " &
                    "AND PRO_Activo = '1'"
        Else
            query = "UPDATE Programacion " &
                    "SET PRO_Activo = '0' " &
                    "WHERE VEH_Codigo = @VEH AND CONVERT(VARCHAR(10), PRO_Fecha, 103) = @FEC " &
                    "AND TUR_Codigo = @TUR AND DCI_Codigo = @DCI " &
                    "AND PRO_Activo = '1'"
        End If

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@VEH", vehiculo)
            .Parameters.AddWithValue("@FEC", fecha)
            .Parameters.AddWithValue("@TUR", turno)
            If dciCodigo <> "" Then
                .Parameters.AddWithValue("@DCI", dciCodigo)
            End If
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Sub eliminarProgramacionXPedido(dciCodigo As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE Programacion " &
                              "SET PRO_Activo = '0' " &
                              "WHERE DCI_Codigo = @DCI AND PRO_Fecha = CONVERT(VARCHAR(10),GETDATE(),103) " &
                              "AND PRO_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@DCI", dciCodigo)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Function obtenerPedidosProgramados(vehiculo As String, fecha As String, turno As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT p.DCI_Codigo, DCI_Estado " &
                              "FROM Programacion p " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.DCI_Codigo = p.DCI_Codigo AND dc.DCI_Programado = '1' AND dc.DCI_Estado = '09' " &
                              "WHERE VEH_Codigo = " & vehiculo &
                              " AND PRO_Fecha = '" & fecha & "' " &
                              "AND TUR_Codigo = " & turno &
                              " AND PRO_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarPedidosSalida(codVehiculo As String, codTurno As String, fecha As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT (CASE WHEN dc.DCI_Estado = '10' THEN 1 ELSE 0 END) AS CH, dc.DCI_Codigo, e.EST_Abreviatura " &
                              "FROM Programacion p " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.DCI_Codigo = p.DCI_Codigo " &
                              "INNER JOIN Estado e " &
                              "ON e.EST_Codigo = dc.DCI_Estado AND (e.EST_Codigo = '09' OR e.EST_Codigo = '10') AND e.EST_Activo = '1' " &
                              "WHERE p.VEH_Codigo = @VEH AND p.TUR_Codigo = @TUR AND " &
                              "p.PRO_Fecha = @FEC AND p.PRO_Activo = '1' AND p.PRO_FecSalida IS NULL"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@VEH", codVehiculo)
            .Parameters.AddWithValue("@TUR", codTurno)
            .Parameters.AddWithValue("@FEC", fecha)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarBultosLargos(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DCB_Cantidad AS Cantidad,DCB_Caja AS Caja,DCB_Observacion AS Obser " &
                              "FROM Documento_Cliente_Bulto " &
                              "WHERE DCI_Codigo = @DCI AND DCB_Activo = '1' " &
                              "UNION " &
                              "SELECT dad.DAD_Cantidad AS Cantidad,'----' AS  Caja,a.ART_Descripcion AS Obser " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen " &
                              "AND dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dad.DCA_Codigo " &
                              "AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "INNER JOIN Articulo a " &
                              "ON a.ART_Codigo = dad.ART_Codigo AND a.ART_Largo = '1' " &
                              "WHERE dcad.DCI_Codigo = @DCI AND dcad.DCAD_Activo = '1'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@DCI", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarSalidaPedidos(codVehiculo As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dc.DCI_Codigo, c.CLI_Descripcion, " &
                              "(CASE WHEN td.TDO_Codigo = 'F' THEN 'FAC' ELSE 'BOL' END) AS TD, " &
                              "'' AS Documento, '' AS Guia, " &
                              "(CASE WHEN cv.CNV_Codigo = 'C' THEN 'CONTADO' " &
                              "WHEN cv.CNV_Codigo = 'F' THEN 'FACTURA' ELSE 'LETRAS' END) AS Condicion, " &
                              "m.MON_Abreviatura,dc.DCI_Total AS Facturado, (CASE WHEN cv.CNV_Codigo = 'C' THEN '0.00' END) AS Pago, " &
                              "(CASE WHEN c.PRC_Codigo = '1' THEN 'L' ELSE 'P' END) AS LimaProv, " &
                              "COUNT(DISTINCT(dcb.DCB_Codigo)) AS CantBul, " &
                              "ISNULL(SUM(dbo.FNX_SumarLargos(dc.CIA,dc.SUC_Codigo,dc.TDOC_Codigo,dc.DCI_Codigo)),0) AS CantLargo, dc.DCI_Observaciones " &
                              "FROM Programacion p " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.DCI_Codigo = p.DCI_Codigo AND dc.DCI_Programado = '1' " &
                              "INNER JOIN Cliente c " &
                              "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "INNER JOIN TipoDocumento td " &
                              "ON td.TDO_Codigo = dc.TDO_Codigo AND td.TDO_Activo = '1' " &
                              "INNER JOIN CondicionVenta cv " &
                              "ON cv.CNV_Codigo = dc.CNV_Codigo AND cv.CNV_Activo = '1' " &
                              "INNER JOIN Moneda m " &
                              "ON m.MON_Codigo = dc.MON_Codigo AND m.MON_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Bulto dcb " &
                              "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo AND dcb.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Detalle dcd " &
                              "ON dcd.CIA = dc.CIA AND dcd.SUC = dc.SUC_Codigo AND dcd.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                              "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC AND dcad.TDOC_Cliente = dcd.TDOC_Codigo " &
                              "AND dcad.DCI_Codigo = dcd.DCI_Codigo AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                              "LEFT JOIN (SELECT dcad.DCA_Codigo, COUNT(*) AS Cantidad " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                              "AND dad.DAD_Codigo = dcad.DAD_Codigo AND DAD_Activo = '1' " &
                              "GROUP BY dcad.DCA_Codigo) AS tabla " &
                              "ON tabla.DCA_Codigo = dcad.DCA_Codigo " &
                              "WHERE p.VEH_Codigo = @VEH AND p.PRO_Activo = '1' AND p.PRO_Fecha = CONVERT(VARCHAR(15), GETDATE(), 103) AND p.PRO_FecLlegada IS NULL " &
                              "GROUP BY dc.DCI_Codigo,c.CLI_Descripcion,td.TDO_Codigo,cv.CNV_Codigo,m.MON_Abreviatura,dc.DCI_Total,c.PRC_Codigo,dc.DCI_Observaciones,tabla.Cantidad"
        'AND p.PRO_FecSalida IS NULL
        'Aumente el ultimo AND  de la penultima linea

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@VEH", codVehiculo)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarArticulosXPedido(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dcad.DCI_Codigo, dcad.DCD_Codigo, a.ART_Descripcion, SUM(dad.DAD_Cantidad) AS Cantidad " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen " &
                              "AND dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                              "AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "INNER JOIN Articulo a " &
                              "ON a.ART_Codigo = dad.ART_Codigo AND a.ART_Activo = '1' " &
                              "WHERE dcad.DCI_Codigo = @PED AND dcad.DCAD_Activo = '1' " &
                              "GROUP BY dcad.DCI_Codigo, dcad.DCD_Codigo, a.ART_Descripcion"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@PED", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    'segunda linea
    Function listarSalidaPedidosXNPED(codVehiculo As String, codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dc.DCI_Codigo, c.CLI_Descripcion, " &
                              "(CASE WHEN td.TDO_Codigo = 'F' THEN 'FAC' ELSE 'BOL' END) AS TD, " &
                              "'' AS Documento, '' AS Guia, " &
                              "(CASE WHEN cv.CNV_Codigo = 'C' THEN 'CONTADO' " &
                              "WHEN cv.CNV_Codigo = 'F' THEN 'FACTURA' ELSE 'LETRAS' END) AS Condicion, " &
                              "m.MON_Abreviatura,dc.DCI_Total AS Facturado,(CASE WHEN cv.CNV_Codigo = 'C' THEN '0.00' END) AS Pago, " &
                              "(CASE WHEN c.CLI_Provincia = '1' THEN 'P' ELSE 'L' END) AS LimaProv, " &
                              "COUNT(DISTINCT(dcb.DCB_Codigo)) AS CantBul, ISNULL(tabla.Cantidad,0) AS CantLargo, dc.DCI_Observaciones " &
                              "FROM Programacion p " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.DCI_Codigo = p.DCI_Codigo  AND dc.DCI_Codigo NOT IN (" & codPedido & ")" &
                              "INNER JOIN Cliente c " &
                              "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "INNER JOIN TipoDocumento td " &
                              "ON td.TDO_Codigo = dc.TDO_Codigo AND td.TDO_Activo = '1' " &
                              "INNER JOIN CondicionVenta cv " &
                              "ON cv.CNV_Codigo = dc.CNV_Codigo AND cv.CNV_Activo = '1' " &
                              "INNER JOIN Moneda m " &
                              "ON m.MON_Codigo = dc.MON_Codigo AND m.MON_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Bulto dcb " &
                              "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo AND dcb.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Detalle dcd " &
                              "ON dcd.CIA = dc.CIA AND dcd.SUC = dc.SUC_Codigo AND dcd.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                              "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC AND dcad.TDOC_Cliente = dcd.TDOC_Codigo " &
                              "AND dcad.DCI_Codigo = dcd.DCI_Codigo AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                              "LEFT JOIN (SELECT dcad.DCA_Codigo, COUNT(*) AS Cantidad " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                              "AND dad.DAD_Codigo = dcad.DAD_Codigo AND DAD_Activo = '1' " &
                              "INNER JOIN Articulo a " &
                              "ON A.ART_Codigo = dad.ART_Codigo AND a.ART_Activo = '1' AND a.ART_Largo = '1' " &
                              "GROUP BY dcad.DCA_Codigo) AS tabla " &
                              "ON tabla.DCA_Codigo = dcad.DCA_Codigo " &
                              "WHERE p.VEH_Codigo = @VEH AND p.PRO_Activo = '1'  " &
                              "GROUP BY dc.DCI_Codigo,c.CLI_Descripcion,td.TDO_Codigo,cv.CNV_Codigo,m.MON_Abreviatura,dc.DCI_Total,c.CLI_Provincia,dc.DCI_Observaciones,tabla.Cantidad"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@VEH", codVehiculo)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarSalidaPedidosXPED(codVehiculo As String, codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dc.DCI_Codigo, c.CLI_Descripcion, " &
                              "(CASE WHEN td.TDO_Codigo = 'F' THEN 'FAC' ELSE 'BOL' END) AS TD, " &
                              "'' AS Documento, '' AS Guia, " &
                              "(CASE WHEN cv.CNV_Codigo = 'C' THEN 'CONTADO' " &
                              "WHEN cv.CNV_Codigo = 'F' THEN 'FACTURA' ELSE 'LETRAS' END) AS Condicion, " &
                              "m.MON_Abreviatura,dc.DCI_Total AS Facturado,(CASE WHEN cv.CNV_Codigo = 'C' THEN '0.00' END) AS Pago, " &
                              "(CASE WHEN c.PRC_Codigo = '1' THEN 'L' ELSE 'P' END) AS LimaProv, " &
                              "COUNT(DISTINCT(dcb.DCB_Codigo)) AS CantBul, " &
                              "ISNULL(SUM(dbo.FNX_SumarLargos(dc.CIA,dc.SUC_Codigo,dc.TDOC_Codigo,dc.DCI_Codigo)),0) AS CantLargo, dc.DCI_Observaciones " &
                              "FROM Programacion p " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.DCI_Codigo = p.DCI_Codigo  AND dc.DCI_Codigo = @PED AND dc.DCI_Programado = '1' " &
                              "INNER JOIN Cliente c " &
                              "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "INNER JOIN TipoDocumento td " &
                              "ON td.TDO_Codigo = dc.TDO_Codigo AND td.TDO_Activo = '1' " &
                              "INNER JOIN CondicionVenta cv " &
                              "ON cv.CNV_Codigo = dc.CNV_Codigo AND cv.CNV_Activo = '1' " &
                              "INNER JOIN Moneda m " &
                              "ON m.MON_Codigo = dc.MON_Codigo AND m.MON_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Bulto dcb " &
                              "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo AND dcb.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Detalle dcd " &
                              "ON dcd.CIA = dc.CIA AND dcd.SUC = dc.SUC_Codigo AND dcd.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                              "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC AND dcad.TDOC_Cliente = dcd.TDOC_Codigo " &
                              "AND dcad.DCI_Codigo = dcd.DCI_Codigo AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                              "LEFT JOIN (SELECT dcad.DCA_Codigo, COUNT(*) AS Cantidad " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                              "AND dad.DAD_Codigo = dcad.DAD_Codigo AND DAD_Activo = '1' " &
                              "INNER JOIN Articulo a " &
                              "ON A.ART_Codigo = dad.ART_Codigo AND a.ART_Activo = '1' AND a.ART_Largo = '1' " &
                              "GROUP BY dcad.DCA_Codigo) AS tabla " &
                              "ON tabla.DCA_Codigo = dcad.DCA_Codigo " &
                              "WHERE p.VEH_Codigo = @VEH AND p.PRO_Activo = '1' AND p.PRO_FecLlegada IS NULL " &
                              "GROUP BY dc.DCI_Codigo,c.CLI_Descripcion,td.TDO_Codigo,cv.CNV_Codigo,m.MON_Abreviatura,dc.DCI_Total,c.PRC_Codigo,dc.DCI_Observaciones,tabla.Cantidad"
        'AND p.PRO_FecSalida IS NULL
        'aumente el ultimo and de la penultima linea

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@VEH", codVehiculo)
            .Parameters.AddWithValue("@PED", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarVehiculosProgramados(fecha As String, turno As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT v.VEH_Codigo, v.VEH_Placa, ISNULL(SUM(tabla1.Bultos),0) AS CantBulto, " &
                              "ISNULL(SUM(dbo.FNX_SumarLargos(dc.CIA,dc.SUC_Codigo,dc.TDOC_Codigo,dc.DCI_Codigo)),0) AS CantLargos, " &
                              "ISNULL(SUM(tabla1.Peso),0.00) AS TotalPeso, '0.00' AS Volumen " &
                              "FROM Vehiculo v " &
                              "INNER JOIN Programacion p " &
                              "ON p.VEH_Codigo = v.VEH_Codigo AND p.PRO_Fecha = '" & fecha & "' AND p.TUR_Codigo = " & turno & " AND p.PRO_Activo = '1' " &
                              "AND p.PRO_FecSalida IS NULL " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON p.DCI_Codigo = dc.DCI_Codigo AND dc.DCI_Estado = '10' " &
                              "INNER JOIN (SELECT dcb.CIA,dcb.SUC_Codigo,dcb.TDOC_Codigo,dcb.DCI_Codigo, " &
                              "COUNT(dcb.DCB_Codigo) AS Bultos,SUM(dcb.DCB_PesoNeto) AS Peso " &
                              "FROM Documento_Cliente_Bulto dcb " &
                              "WHERE dcb.DCB_Activo = '1' " &
                              "GROUP BY dcb.CIA,dcb.SUC_Codigo,dcb.TDOC_Codigo,dcb.DCI_Codigo) AS tabla1 " &
                              "ON tabla1.CIA = dc.CIA AND tabla1.SUC_Codigo = dc.SUC_Codigo AND tabla1.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND tabla1.DCI_Codigo = dc.DCI_Codigo " &
                              "WHERE v.VEH_Activo = '1' " &
                              "GROUP BY v.VEH_Codigo,v.VEH_Placa"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@FEC", fecha)
            .Parameters.AddWithValue("@TUR", turno)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub actualizarFechaSalida(fecha As String, turno As String, vehiculo As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE Programacion " &
                              "SET PRO_FecSalida = GETDATE() " &
                              "WHERE Pro_Fecha = @FEC AND TUR_Codigo = @TUR " &
                              "AND VEH_Codigo = @VEH AND PRO_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@VEH", vehiculo)
            .Parameters.AddWithValue("@FEC", fecha)
            .Parameters.AddWithValue("@TUR", turno)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Sub actualizarFechaLlegada(vehiculo As String, pedido As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE Programacion " &
                              "SET PRO_FecLLegada = GETDATE() " &
                              "WHERE DCI_Codigo = @DCI " &
                              "AND VEH_Codigo = @VEH AND PRO_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@VEH", vehiculo)
            .Parameters.AddWithValue("@DCI", pedido)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Function obtenerProgramacion(fecha As String, turno As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT p.VEH_Codigo, v.VEH_Placa " &
                              "FROM Programacion p " &
                              "INNER JOIN Vehiculo v " &
                              "ON v.VEH_Codigo = p.VEH_Codigo AND v.VEH_Activo = '1' " &
                              "WHERE PRO_Fecha = @Fecha AND TUR_Codigo = @Turno " &
                              "AND PRO_Activo = '1' " &
                              "GROUP BY p.VEH_Codigo, v.VEH_Placa"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@Fecha", fecha)
            .Parameters.AddWithValue("@Turno", turno)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub actualizarOrden(pedido As String, orden As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE Programacion " &
                              "SET PRO_Orden = @ORD " &
                              "WHERE DCI_Codigo = @DCI " &
                              "AND PRO_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@ORD", orden)
            .Parameters.AddWithValue("@DCI", pedido)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Function obtenerNumeroOrden() As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT COUNT(*) + 1 AS Contar FROM Programacion"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim)
    End Function

    Function obtenerDatosProgramacion(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT p.PRO_Fecha, p.PRO_FechaAsig, t.TUR_Descripcion, " &
                              "v.VEH_Placa, p.PRO_FechaCarga, p.PRO_FecSalida, " &
                              "(CASE WHEN dc.DCI_Rebote IS NULL THEN 'No' " &
                               "WHEN dc.DCI_Rebote = 'P' THEN 'Parcial' " &
                               "ELSE 'Total' END) AS Rebote, " &
                              "p.PRO_FecLLegada " &
                              "FROM Programacion p " &
                              "INNER JOIN Turno t " &
                              "ON t.TUR_Codigo = p.TUR_Codigo AND t.TUR_Activo = '1' " &
                              "INNER JOIN Vehiculo v " &
                              "ON v.VEH_Codigo = p.VEH_Codigo AND v.VEH_Activo = '1' " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.DCI_Codigo = p.DCI_Codigo " &
                              "WHERE p.DCI_Codigo = @PED " &
                              "AND p.PRO_Activo = '1'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@PED", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub actualizarFechaCarga(codPedido As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE Programacion " &
                              "SET PRO_FechaCarga = GETDATE() " &
                              "WHERE DCI_Codigo = @PED " &
                              "AND PRO_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@PED", codPedido)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

End Class
